﻿using Dapper;
using EquipmentModule.Contracts;
using EquipmentModule.Entity;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace EquipmentModule.Services
{

    public class EquipmentHistoryService : IEquipmentHistoryService
    {
        public EquipmentItem4Carousel Carousel4Equipment(Guid id, string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            using var db = EquipmentModule.ConnCache.GetConn(dbStr);
            var info = db.Query<Equipment>("SELECT * FROM Md_Equipment WHERE Id=@id", new { id }).FirstOrDefault();
            if (info == null)
            {
                return null;
            }
            EquipmentItem4Carousel equipmentItem4Carousel = new EquipmentItem4Carousel();
            //COD
            var cods = db.Query<double>("SELECT TOP(12)value FROM Hd_EquipmentHistory WHERE DevId=@DevId AND TagName='COD' AND CreateTime <GETDATE() ORDER BY Id DESC", new { info.DevId }).ToList();
            //NH3
            var ans = db.Query<double>("SELECT TOP(12)value FROM Hd_EquipmentHistory WHERE DevId=@DevId AND TagName='氨氮' AND CreateTime <GETDATE() ORDER BY Id DESC", new { info.DevId }).ToList();
            //02
            var dos = db.Query<double>("SELECT TOP(12)value FROM Hd_EquipmentHistory WHERE DevId=@DevId AND TagName='DO' AND CreateTime <GETDATE() ORDER BY Id DESC", new { info.DevId }).ToList();

            var dts = db.Query<string>("SELECT TOP(12) CONVERT(varchar(100), CreateTime, 8) FROM Hd_EquipmentHistory WHERE DevId=@devId AND TagName='DO' AND CreateTime <GETDATE() ORDER BY Id DESC", new { info.DevId }).ToList();
            cods.Reverse();
            ans.Reverse();
            dos.Reverse();
            dts.Reverse();
            equipmentItem4Carousel.COD = cods;
            equipmentItem4Carousel.NH3 = ans;
            equipmentItem4Carousel.O2 = dos;
            equipmentItem4Carousel.Date = dts;
            equipmentItem4Carousel.RiverName = info.Name;
            return equipmentItem4Carousel;
        }

        public List<Guid> Carousel4EquipmentId(string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            using var db = ConnCache.GetConn(dbStr);
            var ids = db.Query<Guid>("SELECT Id FROM Md_Equipment WHERE DevId='沈家宅' OR DevId='中心河'").ToList();
            return ids;
        }

        public List<EquipmentHistoryItem> GetCurrentData(Guid id, string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            using var db = EquipmentModule.ConnCache.GetConn(dbStr);
            //先获取devId
            var devId = db.Query<string>("SELECT DevId FROM Md_Equipment WHERE Id=@id", new { id }).FirstOrDefault();
            if (devId == null)
            {
                return null;
            }
            //
            //return db.Query<EquipmentHistoryItem>("SELECT * FROM Hd_EquipmentHistory WHERE Ticks=(SELECT TOP(1)Ticks FROM Hd_EquipmentHistory WHERE DevId=@devId GROUP BY Ticks ORDER BY Ticks DESC) AND DevId=@devId", new { devId }).ToList();

            return db.Query<EquipmentHistoryItem>("SELECT * FROM Hd_EquipmentHistory WHERE CreateTime=(SELECT TOP(1)CreateTime FROM Hd_EquipmentHistory WHERE DevId=@devId AND CreateTime<GETDATE() GROUP BY CreateTime ORDER BY CreateTime DESC) AND DevId=@devId", new { devId }).ToList();
        }

        public void ImportHistoryData2DB(string fileName, Guid id, string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            //先获取devid
            string devId = null;
            using (var db = EquipmentModule.ConnCache.GetConn(dbStr))
            {
                devId = db.Query<string>("SELECT DevId FROM Md_Equipment WHERE Id=@id", new { id }).FirstOrDefault();
                if (devId == null) return;
            }
            try
            {
                DataTable dt = ExcelHelper.ImportFromExcel(fileName, "0", 0);
                if (dt.Columns.Contains("水体编码"))
                {
                    //日志
                    this.SaveEquipmentLogHistory(dt, dbStr);
                }
                else
                {
                    //历史数据
                    this.SaveEquipmentHistory(dt, devId, dbStr);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        private void SaveEquipmentLogHistory(DataTable dt, string dbStr)
        {
            var db = EquipmentModule.ConnCache.GetConn(dbStr);
            List<RiverHistoryItem> list = new List<RiverHistoryItem>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                RiverHistoryItem info = new RiverHistoryItem();
                info.RiverCode = dt.Rows[i][1].ToString();
                info.RiverName = dt.Rows[i][2].ToString();
                info.Operator = dt.Rows[i][3].ToString();
                info.PH = dt.Rows[i][4].ToString();
                info.O2 = dt.Rows[i][5].ToString();
                info.KMnO4 = dt.Rows[i][6].ToString();
                info.NH3 = dt.Rows[i][7].ToString();
                info.P = dt.Rows[i][8].ToString();
                list.Add(info);
            }
            db.Execute("DELETE FROM Hd_RiverHistory");
            db.Execute("INSERT INTO Hd_RiverHistory(RiverCode,RiverName,Operator,PH,O2,KMnO4,NH3,P,CreateTime) VALUES(@RiverCode,@RiverName,@Operator,@PH,@O2,@KMnO4,@NH3,@P,GETDATE())", list);
        }

        private void SaveEquipmentHistory(DataTable dt, string devId, string dbStr = null)
        {
            var db = EquipmentModule.ConnCache.GetConn(dbStr);
            List<EquipmentHistoryItem> list = new List<EquipmentHistoryItem>();
            DateTime start = DateTime.Now.Date.AddDays(-2);
            for (int i = 0; i < 3; i++)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    var increment = int.Parse(dt.Rows[j][1].ToString());
                    if (increment == 0)
                    {
                        //如果是0 换下一天
                        start = start.Date.AddDays(1);
                    }
                    var createTime = start.AddHours(increment);
                    long t = (long)(createTime - new DateTime(1970, 1, 1)).TotalMilliseconds;
                    EquipmentHistoryItem info4COD = new EquipmentHistoryItem();
                    info4COD.DevId = devId;
                    info4COD.TagName = "COD";
                    info4COD.Value = dt.Rows[j][2];
                    info4COD.CreateTime = createTime;
                    info4COD.Ticks = t;
                    list.Add(info4COD);

                    EquipmentHistoryItem info4AD = new EquipmentHistoryItem();
                    info4AD.DevId = devId;
                    info4AD.TagName = "氨氮";
                    info4AD.Value = dt.Rows[j][3];
                    info4AD.CreateTime = createTime;
                    info4AD.Ticks = t;
                    list.Add(info4AD);

                    EquipmentHistoryItem info4DO = new EquipmentHistoryItem();
                    info4DO.DevId = devId;
                    info4DO.TagName = "DO";
                    info4DO.Value = dt.Rows[j][4];
                    info4DO.CreateTime = createTime;
                    info4DO.Ticks = t;
                    list.Add(info4DO);
                }
            }
            //删除昨天开始之后的数据
            db.Execute("DELETE FROM Hd_EquipmentHistory WHERE DevId=@devId AND CreateTime>=@dt", new { devId, dt = DateTime.Now.Date.AddDays(-1) });
            //保存数据库
            db.Execute("INSERT INTO Hd_EquipmentHistory(DevId,TagName,Value,CreateTime,Ticks) VALUES(@DevId,@TagName,@Value,@CreateTime,@Ticks)", list);
        }



        public IEnumerable<EquipmentHistoryItem> Index(DateTime start, DateTime end, string devId, string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            using var db = EquipmentModule.ConnCache.GetConn(dbStr);
            return db.Query<EquipmentHistoryItem>("SELECT * FROM Hd_EquipmentHistory WHERE DevId=@devId AND CreateTime between @start and @end ORDER BY Id ", new { devId, start, end });
        }
        /// <summary>
        /// 添加设备历史记录
        /// </summary>
        /// <param name="info"></param>
        /// <param name="dbStr"></param>
        public void Add(EquipmentHistoryItem info, string dbStr = null)
        {
            //从Base库获取数据
            dbStr = null;
            using var db = EquipmentModule.ConnCache.GetConn(dbStr);
            db.Execute("INSERT INTO Hd_EquipmentHistory(DevId,TagName,Value,Value1,CreateTime,Ticks) VALUES(@DevId,@TagName,@Value,@Value1,@CreateTime,@Ticks)", info);
        }

        public bool Exist(string deviceId)
        {
            using var db = EquipmentModule.ConnCache.GetConn();
            var count= db.ExecuteScalar<int>("SELECT COUNT(1) FROM Md_Equipment WHERE DevId=@deviceId", new { deviceId });
            return count > 0;
        }
    }
}
